﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Simple.Services.System.QHSEViolationCategory.Models;

public class ViolationCategoryAnalysisService
{
    private readonly SimpleDbContext _context;
    private readonly QHSEViolationCategoryService _violationCategoryService;
    private readonly QHSEXyViolationTypeService _violationTypeService;
    private readonly QHSEDictionaryService _dictionaryService;



    public ViolationCategoryAnalysisService(SimpleDbContext context, QHSEViolationCategoryService violationCategoryService, QHSEXyViolationTypeService violationTypeService, QHSEDictionaryService dictionaryService)
    {
        _context = context;
        _violationCategoryService = violationCategoryService;
        _violationTypeService = violationTypeService;
        _dictionaryService = dictionaryService;
    }
    private static string EscapeLikePattern(string pattern)
    {
        return pattern.Replace("'", "''").Replace("/", "//").Replace("_", "/_").Replace("%", "/%");
    }
    public async Task<List<ViolationAnalysisResult>> ViolationCategoryAnalysisAsync(ViolationSpecialtyAnalysisInput input)
    {
        var cates =await _violationCategoryService.GetSon(input.ViolationCategory);
        var result = new List<ViolationAnalysisResult>();
        var query = _context.Set<QHSESysXyViolation>().AsQueryable();
        if (!string.IsNullOrEmpty(input.OrganizationName) && input.TimeOn != null && input.TimeEnd != null && input.CheckType.Count != 0 && input.WorkSpeciality != null && input.ViolationCategory != null)
        {
            query = query.Where(u => input.CheckType.Contains(u.Check_Type));
            query = query.Where(u => input.WorkSpeciality.Contains(u.WorkSpecialty));
            query = query.Where(u => u.CheckDate >= input.TimeOn && u.CheckDate <= input.TimeEnd);
            var org = await _context.Set<QHSESysOrganization>().Where(u => u.OrganizationId == input.OrganizationId).FirstOrDefaultAsync();
            if (org != null)
            {
                var escapedNamePath = EscapeLikePattern(org.NamePath);
                query = query
                    .Join(_context.Set<QHSESysOrganization>(),
                        violation => violation.OrganizationId,
                        organization => organization.OrganizationId,
                        (violation, organization) => new { Violation = violation, Organization = organization })
                    .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                    .Select(joinResult => joinResult.Violation);
            }
            if (!string.IsNullOrEmpty(input.CheckOrganizationId))
            {
                var corg = await _context.Set<QHSESysOrganization>().Where(u => u.OrganizationId == input.CheckOrganizationId).FirstOrDefaultAsync();
                if (corg != null)
                {
                    var escapedcNamePath = EscapeLikePattern(corg.NamePath);
                    query = query
                            .Join(_context.Set<QHSESysOrganization>(), // Join with QHSEOrganization table
                                joinResult => joinResult.CheckOrganizationId,
                                organization => organization.OrganizationId,
                                (joinResult, organization) => new { Violation = joinResult,  Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"%{escapedcNamePath}%", "/"))
                            .Select(joinResult => joinResult.Violation);
                }
            }
            foreach (var category in cates)
            {
                var children = await _violationCategoryService.GetChildren(category);
                var value = await query.Where(u => children.Contains(u.ViolationCategory)).CountAsync();
                ViolationAnalysisResult res = new ViolationAnalysisResult();
                res.Name = category;
                res.Value = value;
                result.Add(res);

            }
        }
        return result.OrderByDescending(result => result.Value).ToList();
    }
        public async Task<List<ViolationAnalysisResult>> ViolationTypeAnalysisAsync(ViolationSpecialtyAnalysisInput input)
    {
        List<ViolationAnalysisResult> result = new List<ViolationAnalysisResult>();
        var query = _context.Set<QHSESysXyViolation>().AsQueryable();
        if (!string.IsNullOrEmpty(input.OrganizationName) && input.TimeOn != null && input.TimeEnd != null && input.CheckType.Count != 0 && input.WorkSpeciality != null && input.ViolationCategory!=null)
        {
            query = query.Where(u => input.CheckType.Contains(u.Check_Type));
            query = query.Where(u => input.WorkSpeciality.Contains(u.WorkSpecialty));
            query = query.Where(u => u.CheckDate >= input.TimeOn && u.CheckDate <= input.TimeEnd);
            var category = await _violationCategoryService.GetChildren(input.ViolationCategory);
            if (category != null)
            {
                query = query.Where(u => category.Contains(u.ViolationCategory));
            }
            var org = await _context.Set<QHSESysOrganization>().Where(u => u.OrganizationId == input.OrganizationId).FirstOrDefaultAsync();
            if (org != null)
            {
                var escapedNamePath = EscapeLikePattern(org.NamePath);
                query = query
                    .Join(_context.Set<QHSESysOrganization>(),
                        violation => violation.OrganizationId,
                        organization => organization.OrganizationId,
                        (violation, organization) => new { Violation = violation, Organization = organization })
                    .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                    .Select(joinResult => joinResult.Violation);
            }
            if (!string.IsNullOrEmpty(input.CheckOrganizationId))
            {
                var corg = await _context.Set<QHSESysOrganization>().Where(u => u.OrganizationId == input.CheckOrganizationId).FirstOrDefaultAsync();
                if (corg != null)
                {
                    var escapedcNamePath = EscapeLikePattern(corg.NamePath);
                    query = query
                            .Join(_context.Set<QHSESysOrganization>(), // Join with QHSEOrganization table
                                joinResult => joinResult.CheckOrganizationId,
                                organization => organization.OrganizationId,
                                (joinResult, organization) => new { Violation = joinResult, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"%{escapedcNamePath}%", "/"))
                            .Select(joinResult => joinResult.Violation);
                }
            }
            var violationType = new List<QHSEXyViolationTypeModel>();
            violationType = await _violationTypeService.GetAsync();
            if(violationType != null)
            {
                foreach (var type in violationType)
                {
                    ViolationAnalysisResult res = new ViolationAnalysisResult();
                    res.Name = type.ViolationType;
                    res.Value = await query.Where(u => u.ViolationType == type.ViolationType).CountAsync();
                    result.Add(res);
                }
            }
        }
        return result.OrderByDescending(result => result.Value).ToList();
    }
    public async Task<List<ViolationAnalysisResult>> ViolationWorkYearsAnalysisAsync(ViolationSpecialtyAnalysisInput input)
    {
        List<ViolationAnalysisResult> result = new List<ViolationAnalysisResult>();
        var query = _context.Set<QHSESysXyViolation>().AsQueryable();
        if (!string.IsNullOrEmpty(input.OrganizationName) && input.TimeOn != null && input.TimeEnd != null && input.CheckType.Count != 0 && input.WorkSpeciality != null && input.ViolationCategory != null)
        {
            query = query.Where(u => input.CheckType.Contains(u.Check_Type));
            query = query.Where(u => input.WorkSpeciality.Contains(u.WorkSpecialty));
            query = query.Where(u => u.CheckDate >= input.TimeOn && u.CheckDate <= input.TimeEnd);
            var category = await _violationCategoryService.GetChildren(input.ViolationCategory);
            if (category != null)
            {
                query = query.Where(u => category.Contains(u.ViolationCategory));
            }
            var org = await _context.Set<QHSESysOrganization>().Where(u => u.OrganizationId == input.OrganizationId).FirstOrDefaultAsync();
            if (org != null)
            {
                var escapedNamePath = EscapeLikePattern(org.NamePath);
                query = query
                    .Join(_context.Set<QHSESysOrganization>(),
                        violation => violation.OrganizationId,
                        organization => organization.OrganizationId,
                        (violation, organization) => new { Violation = violation, Organization = organization })
                    .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                    .Select(joinResult => joinResult.Violation);
            }
            if (!string.IsNullOrEmpty(input.CheckOrganizationId))
            {
                var corg = await _context.Set<QHSESysOrganization>().Where(u => u.OrganizationId == input.CheckOrganizationId).FirstOrDefaultAsync();
                if (corg != null)
                {
                    var escapedcNamePath = EscapeLikePattern(corg.NamePath);
                    query = query
                        .Join(_context.Set<QHSESysOrganization>(), // Join with QHSEOrganization table
                                joinResult => joinResult.CheckOrganizationId,
                                organization => organization.OrganizationId,
                                (joinResult, organization) => new { Violation = joinResult, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"%{escapedcNamePath}%", "/"))
                            .Select(joinResult => joinResult.Violation);
                }
            }
            List<QHSEDictionaryModel> workYears = await _dictionaryService.GetDictionaryAsync("1003");
            if (workYears != null)
            {
                foreach(var workYear in workYears)
                {
                    var newitem = "";
                    if (workYear.Item == "1年及以下")
                    {
                        newitem = "1年以下";
                    }
                    else if (workYear.Item == "2-3年")
                    {
                        newitem = "1-5年";
                    }
                    else if (workYear.Item == "5年以上")
                    {
                        newitem = "10-15年+15年以上";
                    }
                    var value = await query.Where(u => workYear.Item.Contains(u.WorkYears) || newitem.Contains(u.WorkYears)).CountAsync();
                    ViolationAnalysisResult res = new ViolationAnalysisResult();
                    res.Name = workYear.Item;
                    res.Value = value;
                    result.Add(res);
                }
            }
        }
        return result.OrderByDescending(result => result.Value).ToList();
    }
    public async Task<List<ViolationAnalysisResult>> ViolationAgeAnalysisAsync(ViolationSpecialtyAnalysisInput input)
    {
        List<ViolationAnalysisResult> result = new List<ViolationAnalysisResult>();
        var query = _context.Set<QHSESysXyViolation>().AsQueryable();
        if (!string.IsNullOrEmpty(input.OrganizationName) && input.TimeOn != null && input.TimeEnd != null && input.CheckType.Count != 0 && input.WorkSpeciality != null && input.ViolationCategory != null)
        {
            query = query.Where(u => input.CheckType.Contains(u.Check_Type));
            query = query.Where(u => input.WorkSpeciality.Contains(u.WorkSpecialty));
            query = query.Where(u => u.CheckDate >= input.TimeOn && u.CheckDate <= input.TimeEnd);
            var category = await _violationCategoryService.GetChildren(input.ViolationCategory);
            if (category != null)
            {
                query = query.Where(u => category.Contains(u.ViolationCategory));
            }
            var org = await _context.Set<QHSESysOrganization>().Where(u => u.OrganizationId == input.OrganizationId).FirstOrDefaultAsync();
            if (org != null)
            {
                var escapedNamePath = EscapeLikePattern(org.NamePath);
                query = query
                    .Join(_context.Set<QHSESysOrganization>(),
                        violation => violation.OrganizationId,
                        organization => organization.OrganizationId,
                        (violation, organization) => new { Violation = violation, Organization = organization })
                    .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                    .Select(joinResult => joinResult.Violation);
            }
            if (!string.IsNullOrEmpty(input.CheckOrganizationId))
            {
                var corg = await _context.Set<QHSESysOrganization>().Where(u => u.OrganizationId == input.CheckOrganizationId).FirstOrDefaultAsync();
                if (corg != null)
                {
                    var escapedcNamePath = EscapeLikePattern(corg.NamePath);
                    query = query
                            .Join(_context.Set<QHSESysOrganization>(), // Join with QHSEOrganization table
                                joinResult => joinResult.CheckOrganizationId,
                                organization => organization.OrganizationId,
                                (joinResult, organization) => new { Violation = joinResult, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"%{escapedcNamePath}%", "/"))
                            .Select(joinResult => joinResult.Violation);
                }
            }
            List<QHSEDictionaryModel> ages = await _dictionaryService.GetDictionaryAsync("1002");
            if (ages != null)
            {
                foreach (var age in ages)
                {
                    ViolationAnalysisResult res = new ViolationAnalysisResult();
                    res.Name = age.Item;
                    res.Value = await query.Where(u => u.Age == age.Item).CountAsync();
                    result.Add(res);
                }
            }
        }
        return result.OrderByDescending(result => result.Value).ToList();
    }
    public async Task<List<ViolationAnalysisResult>> ViolationEmployeeTypeAnalysisAsync(ViolationSpecialtyAnalysisInput input)
    {
        List<ViolationAnalysisResult> result = new List<ViolationAnalysisResult>();
        var query = _context.Set<QHSESysXyViolation>().AsQueryable();
        if (!string.IsNullOrEmpty(input.OrganizationName) && input.TimeOn != null && input.TimeEnd != null && input.CheckType.Count != 0 && input.WorkSpeciality != null && input.ViolationCategory != null)
        {
            query = query.Where(u => input.CheckType.Contains(u.Check_Type));
            query = query.Where(u => input.WorkSpeciality.Contains(u.WorkSpecialty));
            query = query.Where(u => u.CheckDate >= input.TimeOn && u.CheckDate <= input.TimeEnd);
            var category = await _violationCategoryService.GetChildren(input.ViolationCategory);
            if (category != null)
            {
                query = query.Where(u => category.Contains(u.ViolationCategory));
            }
            var org = await _context.Set<QHSESysOrganization>().Where(u => u.OrganizationId == input.OrganizationId).FirstOrDefaultAsync();
            if (org != null)
            {
                var escapedNamePath = EscapeLikePattern(org.NamePath);
                query = query
                    .Join(_context.Set<QHSESysOrganization>(),
                        violation => violation.OrganizationId,
                        organization => organization.OrganizationId,
                        (violation, organization) => new { Violation = violation, Organization = organization })
                    .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"{escapedNamePath}%", "/"))
                    .Select(joinResult => joinResult.Violation);
            }
            if (!string.IsNullOrEmpty(input.CheckOrganizationId))
            {
                var corg = await _context.Set<QHSESysOrganization>().Where(u => u.OrganizationId == input.CheckOrganizationId).FirstOrDefaultAsync();
                if (corg != null)
                {
                    var escapedcNamePath = EscapeLikePattern(corg.NamePath);
                    query = query
                            .Join(_context.Set<QHSESysOrganization>(), // Join with QHSEOrganization table
                                joinResult => joinResult.CheckOrganizationId,
                                organization => organization.OrganizationId,
                                (joinResult, organization) => new { Violation = joinResult, Organization = organization })
                            .Where(joinResult => EF.Functions.Like(joinResult.Organization.NamePath, $"%{escapedcNamePath}%", "/"))
                            .Select(joinResult => joinResult.Violation);
                }
            }
            List<QHSEDictionaryModel> employeeType = await _dictionaryService.GetDictionaryAsync("1004");
            if (employeeType != null)
            {
                foreach (var type in employeeType)
                {
                    ViolationAnalysisResult res = new ViolationAnalysisResult();
                    res.Name = type.Item;
                    res.Value = await query.Where(u => u.EmployeeType == type.Item).CountAsync();
                    result.Add(res);
                }
            }
        }
        return result.OrderByDescending(result => result.Value).ToList();
    }
}
